/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package workforce.service;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import org.apache.log4j.Logger;

/**
 *
 * @author TEComp
 */
public class ProcessService {
    private Logger log = Logger.getLogger(this.getClass());
    private Connection conn = null;

    public boolean insert(ProcessSession session) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO PROCESS_SESSION (PROCESS_ID, SESSION_ID, ACTIVITY_ID, FIELD_ID, FIELD_VALUE, VALUE_STEMP) ");
            sql.append("VALUES(?,?,?,?,?,CURRENT_TIMESTAMP");
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, session.getProcessId());
            stmt.setString(2, session.getSessionId());
            stmt.setString(3, session.getActivityId());
            stmt.setString(4, session.getFieldId());
            stmt.setString(5, session.getFieldValue());
            int ret = stmt.executeUpdate();
            stmt.close();
            stmt = null;
            return (ret > 0) ? true : false;
        } catch (SQLException ex) {
            log.fatal(ex.getMessage(), ex);
        }
        return false;
    }

    public boolean insert(String sessionId, String processId, String activityId, Iterator<Cell> iterator) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("INSERT INTO PROCESS_SESSION (PROCESS_ID, SESSION_ID, ACTIVITY_ID, FIELD_ID, FIELD_VALUE, VALUE_STEMP) ");
            sql.append("VALUES(?,?,?,?,?,CURRENT_TIMESTAMP");
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            int count = 0;
            for (count = 0; iterator.hasNext(); count++) {
                Cell cell = iterator.next();
                String key = cell.getKey();
                String vals = cell.getValue();
                vals = (vals == null) ? "" : vals;
                stmt.setString(1, processId);
                stmt.setString(2, sessionId);
                stmt.setString(3, activityId);
                stmt.setString(4, key);
                stmt.setString(5, vals);
                stmt.addBatch();
            }
            int ret[] = stmt.executeBatch();
            stmt.close();
            stmt = null;
            return (ret.length > count) ? true : false;
        } catch (SQLException ex) {
            log.fatal(ex.getMessage(), ex);
        }
        return false;
    }

    public boolean save(ProcessSession session) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE PROCESS_SESSION SET FIELD_VALUE=?, VALUE_STEMP=CURRENT_TIMESTAMP WHERE  ");
            sql.append("PROCESS_ID=?, SESSION_ID=? AND ACTIVITY_ID=? AND FIELD_ID=?");
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            stmt.setString(1, session.getFieldValue());
            stmt.setString(2, session.getProcessId());
            stmt.setString(3, session.getSessionId());
            stmt.setString(4, session.getActivityId());
            stmt.setString(5, session.getFieldId());
            int ret = stmt.executeUpdate();
            stmt.close();
            stmt = null;
            return (ret > 0) ? true : false;
        } catch (SQLException ex) {
            log.fatal(ex.getMessage(), ex);
        }
        return false;
    }

    public boolean save(String sessionId, String processId, String activityId, Iterator<Cell> iterator) {
        try {
            StringBuilder sql = new StringBuilder();
            sql.append("UPDATE PROCESS_SESSION SET FIELD_VALUE=?, VALUE_STEMP=CURRENT_TIMESTAMP WHERE  ");
            sql.append("PROCESS_ID=?, SESSION_ID=? AND ACTIVITY_ID=? AND FIELD_ID=?");
            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            int count = 0;
            for (count = 0; iterator.hasNext(); count++) {
                Cell cell = iterator.next();
                String key = cell.getKey();
                String vals = cell.getValue();
                vals = (vals == null) ? "" : vals;
                stmt.setString(1, vals);
                stmt.setString(2, processId);
                stmt.setString(3, sessionId);
                stmt.setString(4, activityId);
                stmt.setString(5, key);
                stmt.addBatch();
            }
            int ret[] = stmt.executeBatch();
            stmt.close();
            stmt = null;
            return (ret.length == count) ? true : false;
        } catch (SQLException ex) {
            log.fatal(ex.getMessage(), ex);
        }
        return false;
    }

    public Iterator<Cell> findValueInSession(String sessionId, String processId, String activityId, String[] keys) {
        List<Cell> list = new ArrayList<Cell>();
        try {

            StringBuilder es = new StringBuilder();
            for (int i = 0; i < keys.length; i++) {

                if (i > 0) {
                    es.append(",");
                }
                es.append("'").append(keys[i]).append("'");
            }
            StringBuilder sql = new StringBuilder();
            sql.append("SELECT FIELD_ID, FIELD_VALUE FROM process_session WHERE PROCESS_ID = ? ");
            sql.append("AND SESSION_ID = ? AND ACTIVITY_ID = ? AND FIELD_ID in(");
            sql.append(es).append(")");

            PreparedStatement stmt = conn.prepareStatement(sql.toString());

            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                String key = rs.getString("FIELD_ID");
                String vals = rs.getString("FIELD_VALUE");
                vals = (vals == null) ? "" : vals;
                list.add(new Cell(key, vals));
            }
            rs.close();
            stmt.close();
            rs = null;
            stmt = null;
            return list.iterator();
        } catch (SQLException ex) {
            log.fatal(ex.getMessage(), ex);
        }
        return list.iterator();
    }

    public void close() {
        try {
            if (conn != null) {
                conn.close();
                conn = null;
            }
        } catch (Exception ex) {
            ex.printStackTrace();
        }
    }
}
